/* Extracting BOS Data to csv. */


use IDI_Clean_20181020





-------------------------------------------------------------
-- Data set of firm-March year observations of BOS data 
-------------------------------------------------------------

create table #ent_bos_competition(
		enterprise_nbr			char(10)		NOT NULL,
		dim_year_key			int				NOT NULL,
		selection_weight		FLOAT			NOT NULL,
		final_weight			FLOAT			NOT NULL,
		adjusted_weight			FLOAT			NOT NULL,	
		comp				tinyint			NOT NULL,
		prody				TINYINT			NOT NULL,
		profity				TINYINT			NOT NULL,
		rec_manag			TINYINT			NOT NULL,
		rec_tech			TINYINT			NOT NULL,
		rec_trad			TINYINT			NOT NULL,
		rec_other			TINYINT			NOT NULL,
		collec_emp			TINYINT			NOT NULL,
		any_internat			TINYINT			NOT NULL,
		perc_internat			FLOAT			NOT NULL,
		emp_manag			SMALLINT		NOT NULL,
		emp_tech			SMALLINT		NOT NULL,
		emp_trad			SMALLINT		NOT NULL,
		emp_other			SMALLINT		NOT NULL
				primary key clustered(enterprise_nbr,dim_year_key))

insert into #ent_bos_competition(enterprise_nbr,dim_year_key,selection_weight, final_weight, adjusted_weight, comp, prody, 
	profity, rec_manag, rec_tech, rec_trad, rec_other, collec_emp, any_internat, perc_internat, emp_manag, emp_tech, 
	emp_trad, emp_other)
select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	comp,
	prody,
	profity,
	rec_manag,
	rec_tech,
	rec_trad,
	rec_other,
	collec_emp,
	any_internat,
	perc_internat,
	emp_manag,
	emp_tech,
	emp_trad,
	emp_other
from 
  ((select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	-- level of competition
	comp=case when A4700_01_I=1			THEN 0				-- imputed 
			WHEN				A4700_05=1 then 5		-- don't know response
			  when				A4700_04=1 then 4
			  when				A4700_03=1 then 3
			  when				A4700_02=1 then 2
			  when				A4700_01=1 then 1
							else 0 end,			--No response
	-- relative productivity
	prody = CASE WHEN			A3802_01_I=1 THEN 0			-- imputed 
				WHEN			A3802_04=1 THEN 4		--  don't know
				WHEN			  A3802_03=1 THEN 3
				WHEN			  A3802_02=1 THEN 2
				WHEN			  A3802_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN			A3801_01_I=1 THEN 0			-- imputed
				WHEN				A3801_04=1 THEN 4		--  don't know
				WHEN			  A3801_03=1 THEN 3
				WHEN			  A3801_02=1 THEN 2
				WHEN			  A3801_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		A3301_01_I=1 THEN 0			-- imputed
				WHEN			A3301_01=1   THEN 1			-- no difficulty (??)
				WHEN			A3301_02=1	 THEN 2			-- moderate difficulty
				WHEN			A3301_03=1	 THEN 3			-- severe difficulty
				WHEN			A3301_04=1	 THEN 4			-- not applicable
				WHEN			A3301_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		A3302_01_I=1 THEN 0			-- imputed
				WHEN			A3302_01=1   THEN 1			-- no difficulty (??)
				WHEN			A3302_02=1	 THEN 2			-- moderate difficulty
				WHEN			A3302_03=1	 THEN 3			-- severe difficulty
				WHEN			A3302_04=1	 THEN 4			-- not applicable
				WHEN			A3302_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		A3303_01_I=1 THEN 0			-- imputed
				WHEN			A3303_01=1   THEN 1			-- no difficulty (??)
				WHEN			A3303_02=1	 THEN 2			-- moderate difficulty
				WHEN			A3303_03=1	 THEN 3			-- severe difficulty
				WHEN			A3303_04=1	 THEN 4			-- not applicable
				WHEN			A3303_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		A3304_01_I=1 THEN 0			-- imputed
				WHEN			A3304_01=1   THEN 1			-- no difficulty (??)
				WHEN			A3304_02=1	 THEN 2			-- moderate difficulty
				WHEN			A3304_03=1	 THEN 3			-- severe difficulty
				WHEN			A3304_04=1	 THEN 4			-- not applicable
				WHEN			A3304_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		A3600_01_I=1 THEN 0			-- imputed
				WHEN			A3600_01=1	 THEN 1			-- zero
				WHEN			A3600_02=1	 THEN 2			-- 10% or less
				WHEN			A3600_03=1	 THEN 3			-- 50% or less
				WHEN			A3600_04=1	 THEN 4			-- 90% or less
				WHEN			A3600_05=1	 THEN 5			-- 91-100%
				WHEN			A3600_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	A2600_01_I=1	THEN 0		-- imputed
				WHEN			A2600_01=1		THEN 2		-- yes
				WHEN			A2600_02=1		THEN 1		-- no
				WHEN			A2600_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	A2601_I=1		THEN 999	--imputed
				WHEN			A2601 IS NOT NULL THEN CONVERT(FLOAT,A2601) -- percentage when > 0
				WHEN			A2600_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(A3201,0),
	emp_tech = ISNULL(A3211,0),
	emp_trad = ISNULL(A3221,0),
	emp_other = ISNULL(A3231,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2005_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when				 LA4700_01_I=1 THEN 0
				WHEN				LA4700_05=1 then 5		
			  when				    LA4700_04=1 then 4
			  when				    LA4700_03=1 then 3
			  when				    LA4700_02=1 then 2
			  when				    LA4700_01=1 then 1
												else 0 end,	
	-- relative productivity
	prody = CASE WHEN				LA3802_01_I=1 THEN 0 
				WHEN				LA3802_04=1 THEN 4		
				WHEN			  LA3802_03=1 THEN 3
				WHEN			  LA3802_02=1 THEN 2
				WHEN			  LA3802_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA3801_01_I=1 THEN 0
				WHEN				LA3801_04=1 THEN 4		
				WHEN			  LA3801_03=1 THEN 3
				WHEN			  LA3801_02=1 THEN 2
				WHEN			  LA3801_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA3301_01_I=1 THEN 0			-- imputed
				WHEN			LA3301_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3301_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3301_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3301_04=1	 THEN 4			-- not applicable
				WHEN			LA3301_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA3302_01_I=1 THEN 0			-- imputed
				WHEN			LA3302_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3302_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3302_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3302_04=1	 THEN 4			-- not applicable
				WHEN			LA3302_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA3303_01_I=1 THEN 0			-- imputed
				WHEN			LA3303_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3303_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3303_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3303_04=1	 THEN 4			-- not applicable
				WHEN			LA3303_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA3304_01_I=1 THEN 0			-- imputed
				WHEN			LA3304_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3304_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3304_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3304_04=1	 THEN 4			-- not applicable
				WHEN			LA3304_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA3600_01_I=1 THEN 0			-- imputed
				WHEN			LA3600_01=1	 THEN 1			-- zero
				WHEN			LA3600_02=1	 THEN 2			-- 10% or less
				WHEN			LA3600_03=1	 THEN 3			-- 50% or less
				WHEN			LA3600_04=1	 THEN 4			-- 90% or less
				WHEN			LA3600_05=1	 THEN 5			-- 91-100%
				WHEN			LA3600_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA2600_01_I=1	THEN 0		-- imputed
				WHEN			LA2600_01=1		THEN 2		-- yes
				WHEN			LA2600_02=1		THEN 1		-- no
				WHEN			LA2600_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA2601_I=1		THEN 999	--imputed
				WHEN			LA2601 IS NOT NULL THEN CONVERT(FLOAT,LA2601) -- percentage when > 0
				WHEN			LA2600_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA3201,0),
	emp_tech = ISNULL(LA3202,0),
	emp_trad = ISNULL(LA3203,0),
	emp_other = ISNULL(LA3204,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2006_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA4700_01_I=1 THEN 0 
				WHEN				LA4700_05=1 then 5		
			  when				    LA4700_04=1 then 4
			  when				    LA4700_03=1 then 3
			  when				    LA4700_02=1 then 2
			  when				    LA4700_01=1 then 1
												else 0 end,	
	-- relative productivity
	prody = CASE WHEN				LA3802_01_I=1 THEN 0
				WHEN				LA3802_04=1 THEN 4		
				WHEN			  LA3802_03=1 THEN 3
				WHEN			  LA3802_02=1 THEN 2
				WHEN			  LA3802_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA3801_01_I=1 THEN 0
				WHEN				LA3801_04=1 THEN 4		
				WHEN			  LA3801_03=1 THEN 3
				WHEN			  LA3801_02=1 THEN 2
				WHEN			  LA3801_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA3301_01_I=1 THEN 0			-- imputed
				WHEN			LA3301_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3301_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3301_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3301_04=1	 THEN 4			-- not applicable
				WHEN			LA3301_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA3302_01_I=1 THEN 0			-- imputed
				WHEN			LA3302_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3302_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3302_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3302_04=1	 THEN 4			-- not applicable
				WHEN			LA3302_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA3303_01_I=1 THEN 0			-- imputed
				WHEN			LA3303_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3303_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3303_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3303_04=1	 THEN 4			-- not applicable
				WHEN			LA3303_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA3304_01_I=1 THEN 0			-- imputed
				WHEN			LA3304_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3304_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3304_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3304_04=1	 THEN 4			-- not applicable
				WHEN			LA3304_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA3600_01_I=1 THEN 0			-- imputed
				WHEN			LA3600_01=1	 THEN 1			-- zero
				WHEN			LA3600_02=1	 THEN 2			-- 10% or less
				WHEN			LA3600_03=1	 THEN 3			-- 50% or less
				WHEN			LA3600_04=1	 THEN 4			-- 90% or less
				WHEN			LA3600_05=1	 THEN 5			-- 91-100%
				WHEN			LA3600_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA2600_01_I=1	THEN 0		-- imputed
				WHEN			LA2600_01=1		THEN 2		-- yes
				WHEN			LA2600_02=1		THEN 1		-- no
				WHEN			LA2600_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA2601_I=1		THEN 999	--imputed
				WHEN			LA2601 IS NOT NULL THEN CONVERT(FLOAT,LA2601) -- percentage when > 0
				WHEN			LA2600_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA3201,0),
	emp_tech = ISNULL(LA3202,0),
	emp_trad = ISNULL(LA3203,0),
	emp_other = ISNULL(LA3204,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2007_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA4700_01_I=1 THEN 0
				WHEN				LA4700_05=1 then 5		
			  when				    LA4700_04=1 then 4
			  when				    LA4700_03=1 then 3
			  when				    LA4700_02=1 then 2
			  when				    LA4700_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA3802_01_I=1 THEN 0
				WHEN				LA3802_04=1 THEN 4		
				WHEN			  LA3802_03=1 THEN 3
				WHEN			  LA3802_02=1 THEN 2
				WHEN			  LA3802_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA3801_01_I=1 THEN 0
				WHEN				LA3801_04=1 THEN 4		
				WHEN			  LA3801_03=1 THEN 3
				WHEN			  LA3801_02=1 THEN 2
				WHEN			  LA3801_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA3301_01_I=1 THEN 0			-- imputed
				WHEN			LA3301_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3301_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3301_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3301_04=1	 THEN 4			-- not applicable
				WHEN			LA3301_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA3302_01_I=1 THEN 0			-- imputed
				WHEN			LA3302_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3302_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3302_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3302_04=1	 THEN 4			-- not applicable
				WHEN			LA3302_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA3303_01_I=1 THEN 0			-- imputed
				WHEN			LA3303_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3303_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3303_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3303_04=1	 THEN 4			-- not applicable
				WHEN			LA3303_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA3304_01_I=1 THEN 0			-- imputed
				WHEN			LA3304_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA3304_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA3304_03=1	 THEN 3			-- severe difficulty
				WHEN			LA3304_04=1	 THEN 4			-- not applicable
				WHEN			LA3304_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA3600_01_I=1 THEN 0			-- imputed
				WHEN			LA3600_01=1	 THEN 1			-- zero
				WHEN			LA3600_02=1	 THEN 2			-- 10% or less
				WHEN			LA3600_03=1	 THEN 3			-- 50% or less
				WHEN			LA3600_04=1	 THEN 4			-- 90% or less
				WHEN			LA3600_05=1	 THEN 5			-- 91-100%
				WHEN			LA3600_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA2600_01_I=1	THEN 0		-- imputed
				WHEN			LA2600_01=1		THEN 2		-- yes
				WHEN			LA2600_02=1		THEN 1		-- no
				WHEN			LA2600_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA2601_I=1		THEN 999	--imputed
				WHEN			LA2601 IS NOT NULL THEN CONVERT(FLOAT,LA2601) -- percentage when > 0
				WHEN			LA2600_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA3201,0),
	emp_tech = ISNULL(LA3202,0),
	emp_trad = ISNULL(LA3203,0),
	emp_other = ISNULL(LA3204,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2008_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2900_01_I=1 THEN 0
				WHEN				LA2900_05=1 then 5		
			  when				    LA2900_04=1 then 4
			  when				    LA2900_03=1 then 3
			  when				    LA2900_02=1 then 2
			  when				    LA2900_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA2002_01_I=1 THEN 0 
				WHEN				LA2002_04=1 THEN 4		
				WHEN			  LA2002_03=1 THEN 3
				WHEN			  LA2002_02=1 THEN 2
				WHEN			  LA2002_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA2001_01_I=1 THEN 0 
				WHEN				LA2001_04=1 THEN 4		
				WHEN			  LA2001_03=1 THEN 3
				WHEN			  LA2001_02=1 THEN 2
				WHEN			  LA2001_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1801_01_I=1 THEN 0			-- imputed
				WHEN			LA1801_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1801_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1801_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1801_04=1	 THEN 4			-- not applicable
				WHEN			LA1801_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1802_01_I=1 THEN 0			-- imputed
				WHEN			LA1802_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1802_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1802_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1802_04=1	 THEN 4			-- not applicable
				WHEN			LA1802_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1803_01_I=1 THEN 0			-- imputed
				WHEN			LA1803_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1803_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1803_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1803_04=1	 THEN 4			-- not applicable
				WHEN			LA1803_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1804_01_I=1 THEN 0			-- imputed
				WHEN			LA1804_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1804_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1804_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1804_04=1	 THEN 4			-- not applicable
				WHEN			LA1804_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1900_01_I=1 THEN 0			-- imputed
				WHEN			LA1900_01=1	 THEN 1			-- zero
				WHEN			LA1900_02=1	 THEN 2			-- 10% or less
				WHEN			LA1900_03=1	 THEN 3			-- 50% or less
				WHEN			LA1900_04=1	 THEN 4			-- 90% or less
				WHEN			LA1900_05=1	 THEN 5			-- 91-100%
				WHEN			LA1900_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1300_01_I=1	THEN 0		-- imputed
				WHEN			LA1300_01=1		THEN 2		-- yes
				WHEN			LA1300_02=1		THEN 1		-- no
				WHEN			LA1300_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1301_I=1		THEN 999	--imputed
				WHEN			LA1301 IS NOT NULL THEN CONVERT(FLOAT,LA1301) -- percentage when > 0
				WHEN			LA1300_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1701,0),
	emp_tech = ISNULL(LA1702,0),
	emp_trad = ISNULL(LA1703,0),
	emp_other = ISNULL(LA1704,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2009_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					 LA2900_01_I=1 THEN 0
				WHEN				LA2900_05=1 then 5		
			  when				    LA2900_04=1 then 4
			  when				    LA2900_03=1 then 3
			  when				    LA2900_02=1 then 2
			  when				    LA2900_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA2002_01_I=1 THEN 0 
				WHEN				LA2002_04=1 THEN 4		
				WHEN			  LA2002_03=1 THEN 3
				WHEN			  LA2002_02=1 THEN 2
				WHEN			  LA2002_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA2001_01_I=1 THEN 0
				WHEN				LA2001_04=1 THEN 4		
				WHEN			  LA2001_03=1 THEN 3
				WHEN			  LA2001_02=1 THEN 2
				WHEN			  LA2001_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1801_01_I=1 THEN 0			-- imputed
				WHEN			LA1801_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1801_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1801_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1801_04=1	 THEN 4			-- not applicable
				WHEN			LA1801_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1802_01_I=1 THEN 0			-- imputed
				WHEN			LA1802_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1802_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1802_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1802_04=1	 THEN 4			-- not applicable
				WHEN			LA1802_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1803_01_I=1 THEN 0			-- imputed
				WHEN			LA1803_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1803_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1803_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1803_04=1	 THEN 4			-- not applicable
				WHEN			LA1803_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1804_01_I=1 THEN 0			-- imputed
				WHEN			LA1804_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1804_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1804_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1804_04=1	 THEN 4			-- not applicable
				WHEN			LA1804_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1900_01_I=1 THEN 0			-- imputed
				WHEN			LA1900_01=1	 THEN 1			-- zero
				WHEN			LA1900_02=1	 THEN 2			-- 10% or less
				WHEN			LA1900_03=1	 THEN 3			-- 50% or less
				WHEN			LA1900_04=1	 THEN 4			-- 90% or less
				WHEN			LA1900_05=1	 THEN 5			-- 91-100%
				WHEN			LA1900_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1300_01_I=1	THEN 0		-- imputed
				WHEN			LA1300_01=1		THEN 2		-- yes
				WHEN			LA1300_02=1		THEN 1		-- no
				WHEN			LA1300_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1301_I=1		THEN 999	--imputed
				WHEN			LA1301 IS NOT NULL THEN CONVERT(FLOAT,LA1301) -- percentage when > 0
				WHEN			LA1300_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1701,0),
	emp_tech = ISNULL(LA1702,0),
	emp_trad = ISNULL(LA1703,0),
	emp_other = ISNULL(LA1704,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2010_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2900_01_I=1 THEN 0 
				WHEN				LA2900_05=1 then 5		
			  when				    LA2900_04=1 then 4
			  when				    LA2900_03=1 then 3
			  when				    LA2900_02=1 then 2
			  when				    LA2900_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA2002_01_I=1 THEN 0 
				WHEN				LA2002_04=1 THEN 4		
				WHEN			  LA2002_03=1 THEN 3
				WHEN			  LA2002_02=1 THEN 2
				WHEN			  LA2002_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA2001_01_I=1 THEN 0 
				WHEN				LA2001_04=1 THEN 4		
				WHEN			  LA2001_03=1 THEN 3
				WHEN			  LA2001_02=1 THEN 2
				WHEN			  LA2001_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1801_01_I=1 THEN 0			-- imputed
				WHEN			LA1801_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1801_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1801_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1801_04=1	 THEN 4			-- not applicable
				WHEN			LA1801_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1802_01_I=1 THEN 0			-- imputed
				WHEN			LA1802_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1802_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1802_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1802_04=1	 THEN 4			-- not applicable
				WHEN			LA1802_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1803_01_I=1 THEN 0			-- imputed
				WHEN			LA1803_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1803_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1803_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1803_04=1	 THEN 4			-- not applicable
				WHEN			LA1803_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1804_01_I=1 THEN 0			-- imputed
				WHEN			LA1804_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1804_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1804_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1804_04=1	 THEN 4			-- not applicable
				WHEN			LA1804_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1900_01_I=1 THEN 0			-- imputed
				WHEN			LA1900_01=1	 THEN 1			-- zero
				WHEN			LA1900_02=1	 THEN 2			-- 10% or less
				WHEN			LA1900_03=1	 THEN 3			-- 50% or less
				WHEN			LA1900_04=1	 THEN 4			-- 90% or less
				WHEN			LA1900_05=1	 THEN 5			-- 91-100%
				WHEN			LA1900_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1300_01_I=1	THEN 0		-- imputed
				WHEN			LA1300_01=1		THEN 2		-- yes
				WHEN			LA1300_02=1		THEN 1		-- no
				WHEN			LA1300_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1301_I=1		THEN 999	--imputed
				WHEN			LA1301 IS NOT NULL THEN CONVERT(FLOAT,LA1301) -- percentage when > 0
				WHEN			LA1300_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1701,0),
	emp_tech = ISNULL(LA1702,0),
	emp_trad = ISNULL(LA1703,0),
	emp_other = ISNULL(LA1704,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2011_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2800_01_I=1 THEN 0
				WHEN				LA2800_05=1 then 5		
			  when				    LA2800_04=1 then 4
			  when				    LA2800_03=1 then 3
			  when				    LA2800_02=1 then 2
			  when				    LA2800_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA2008_01_I=1 THEN 0 
				WHEN				LA2008_04=1 THEN 4		
				WHEN			  LA2008_03=1 THEN 3
				WHEN			  LA2008_02=1 THEN 2
				WHEN			  LA2008_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				 LA2007_01_I=1 THEN 0
				WHEN				LA2007_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA2007_03=1 THEN 3
				WHEN			  LA2007_02=1 THEN 2
				WHEN			  LA2007_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1801_01_I=1 THEN 0			-- imputed
				WHEN			LA1801_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1801_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1801_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1801_04=1	 THEN 4			-- not applicable
				WHEN			LA1801_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1802_01_I=1 THEN 0			-- imputed
				WHEN			LA1802_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1802_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1802_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1802_04=1	 THEN 4			-- not applicable
				WHEN			LA1802_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1803_01_I=1 THEN 0			-- imputed
				WHEN			LA1803_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1803_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1803_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1803_04=1	 THEN 4			-- not applicable
				WHEN			LA1803_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1804_01_I=1 THEN 0			-- imputed
				WHEN			LA1804_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1804_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1804_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1804_04=1	 THEN 4			-- not applicable
				WHEN			LA1804_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1900_01_I=1 THEN 0			-- imputed
				WHEN			LA1900_01=1	 THEN 1			-- zero
				WHEN			LA1900_02=1	 THEN 2			-- 10% or less
				WHEN			LA1900_03=1	 THEN 3			-- 50% or less
				WHEN			LA1900_04=1	 THEN 4			-- 90% or less
				WHEN			LA1900_05=1	 THEN 5			-- 91-100%
				WHEN			LA1900_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1300_01_I=1	THEN 0		-- imputed
				WHEN			LA1300_01=1		THEN 2		-- yes
				WHEN			LA1300_02=1		THEN 1		-- no
				WHEN			LA1300_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1301_I=1		THEN 999	--imputed
				WHEN			LA1301 IS NOT NULL THEN CONVERT(FLOAT,LA1301) -- percentage when > 0
				WHEN			LA1300_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1701,0),
	emp_tech = ISNULL(LA1702,0),
	emp_trad = ISNULL(LA1703,0),
	emp_other = ISNULL(LA1704,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2012_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2800_01_I=1 THEN 0
				WHEN				LA2800_05=1 then 5		
			  when				    LA2800_04=1 then 4
			  when				    LA2800_03=1 then 3
			  when				    LA2800_02=1 then 2
			  when				    LA2800_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA2008_01_I=1 THEN 0 
				WHEN				LA2008_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA2008_03=1 THEN 3
				WHEN			  LA2008_02=1 THEN 2
				WHEN			  LA2008_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA2007_01_I=1 THEN 0 
				WHEN				LA2007_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA2007_03=1 THEN 3
				WHEN			  LA2007_02=1 THEN 2
				WHEN			  LA2007_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1801_01_I=1 THEN 0			-- imputed
				WHEN			LA1801_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1801_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1801_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1801_04=1	 THEN 4			-- not applicable
				WHEN			LA1801_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1802_01_I=1 THEN 0			-- imputed
				WHEN			LA1802_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1802_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1802_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1802_04=1	 THEN 4			-- not applicable
				WHEN			LA1802_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1803_01_I=1 THEN 0			-- imputed
				WHEN			LA1803_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1803_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1803_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1803_04=1	 THEN 4			-- not applicable
				WHEN			LA1803_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1804_01_I=1 THEN 0			-- imputed
				WHEN			LA1804_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1804_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1804_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1804_04=1	 THEN 4			-- not applicable
				WHEN			LA1804_05=1	 THEN 5			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1900_01_I=1 THEN 0			-- imputed
				WHEN			LA1900_01=1	 THEN 1			-- zero
				WHEN			LA1900_02=1	 THEN 2			-- 10% or less
				WHEN			LA1900_03=1	 THEN 3			-- 50% or less
				WHEN			LA1900_04=1	 THEN 4			-- 90% or less
				WHEN			LA1900_05=1	 THEN 5			-- 91-100%
				WHEN			LA1900_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1300_01_I=1	THEN 0		-- imputed
				WHEN			LA1300_01=1		THEN 2		-- yes
				WHEN			LA1300_02=1		THEN 1		-- no
				WHEN			LA1300_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1301_I=1		THEN 999	--imputed
				WHEN			LA1301 IS NOT NULL THEN CONVERT(FLOAT,LA1301) -- percentage when > 0
				WHEN			LA1300_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1701,0),
	emp_tech = ISNULL(LA1702,0),
	emp_trad = ISNULL(LA1703,0),
	emp_other = ISNULL(LA1704,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2013_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2700_01_I=1 THEN 0
				WHEN				LA2700_05=1 then 5		
			  when				    LA2700_04=1 then 4
			  when				    LA2700_03=1 then 3
			  when				    LA2700_02=1 then 2
			  when				    LA2700_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA1908_01_I=1 THEN 0 
				WHEN				LA1908_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA1908_03=1 THEN 3
				WHEN			  LA1908_02=1 THEN 2
				WHEN			  LA1908_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA1907_01_I=1 THEN 0 
				WHEN				LA1907_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA1907_03=1 THEN 3
				WHEN			  LA1907_02=1 THEN 2
				WHEN			  LA1907_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1701_01_I=1 THEN 0			-- imputed
				WHEN			LA1701_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1701_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1701_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1701_04=1	 THEN 5			-- not applicable
				WHEN			LA1701_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1702_01_I=1 THEN 0			-- imputed
				WHEN			LA1702_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1702_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1702_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1702_04=1	 THEN 5			-- not applicable
				WHEN			LA1702_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1703_01_I=1 THEN 0			-- imputed
				WHEN			LA1703_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1703_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1703_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1703_04=1	 THEN 5			-- not applicable
				WHEN			LA1703_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1704_01_I=1 THEN 0			-- imputed
				WHEN			LA1704_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1704_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1704_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1704_04=1	 THEN 5			-- not applicable
				WHEN			LA1704_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1800_01_I=1 THEN 0			-- imputed
				WHEN			LA1800_01=1	 THEN 1			-- zero
				WHEN			LA1800_02=1	 THEN 2			-- 10% or less
				WHEN			LA1800_03=1	 THEN 3			-- 50% or less
				WHEN			LA1800_04=1	 THEN 4			-- 90% or less
				WHEN			LA1800_05=1	 THEN 5			-- 91-100%
				WHEN			LA1800_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1200_01_I=1	THEN 0		-- imputed
				WHEN			LA1200_01=1		THEN 2		-- yes
				WHEN			LA1200_02=1		THEN 1		-- no
				WHEN			LA1200_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1201_I=1		THEN 999	--imputed
				WHEN			LA1201 IS NOT NULL THEN CONVERT(FLOAT,LA1201) -- percentage when > 0
				WHEN			LA1200_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1601,0),
	emp_tech = ISNULL(LA1602,0),
	emp_trad = ISNULL(LA1603,0),
	emp_other = ISNULL(LA1604,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2014_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2700_01_I=1 THEN 0
				WHEN				LA2700_05=1 then 5		
			  when				    LA2700_04=1 then 4
			  when				    LA2700_03=1 then 3
			  when				    LA2700_02=1 then 2
			  when				    LA2700_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA1908_01_I=1 THEN 0 
				WHEN				LA1908_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA1908_03=1 THEN 3
				WHEN			  LA1908_02=1 THEN 2
				WHEN			  LA1908_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA1907_01_I=1 THEN 0 
				WHEN				LA1907_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA1907_03=1 THEN 3
				WHEN			  LA1907_02=1 THEN 2
				WHEN			  LA1907_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1701_01_I=1 THEN 0			-- imputed
				WHEN			LA1701_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1701_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1701_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1701_04=1	 THEN 5			-- not applicable
				WHEN			LA1701_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1702_01_I=1 THEN 0			-- imputed
				WHEN			LA1702_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1702_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1702_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1702_04=1	 THEN 5			-- not applicable
				WHEN			LA1702_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1703_01_I=1 THEN 0			-- imputed
				WHEN			LA1703_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1703_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1703_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1703_04=1	 THEN 5			-- not applicable
				WHEN			LA1703_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1704_01_I=1 THEN 0			-- imputed
				WHEN			LA1704_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1704_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1704_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1704_04=1	 THEN 5			-- not applicable
				WHEN			LA1704_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1800_01_I=1 THEN 0			-- imputed
				WHEN			LA1800_01=1	 THEN 1			-- zero
				WHEN			LA1800_02=1	 THEN 2			-- 10% or less
				WHEN			LA1800_03=1	 THEN 3			-- 50% or less
				WHEN			LA1800_04=1	 THEN 4			-- 90% or less
				WHEN			LA1800_05=1	 THEN 5			-- 91-100%
				WHEN			LA1800_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1200_01_I=1	THEN 0		-- imputed
				WHEN			LA1200_01=1		THEN 2		-- yes
				WHEN			LA1200_02=1		THEN 1		-- no
				WHEN			LA1200_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1201_I=1		THEN 999	--imputed
				WHEN			LA1201 IS NOT NULL THEN CONVERT(FLOAT,LA1201) -- percentage when > 0
				WHEN			LA1200_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1601,0),
	emp_tech = ISNULL(LA1602,0),
	emp_trad = ISNULL(LA1603,0),
	emp_other = ISNULL(LA1604,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2015_mod_a)
union 
   (select
	enterprise_nbr,
	dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	response_code,
	comp=case when					LA2700_01_I=1 THEN 0
				WHEN				LA2700_05=1 then 5		
			  when				    LA2700_04=1 then 4
			  when				    LA2700_03=1 then 3
			  when				    LA2700_02=1 then 2
			  when				    LA2700_01=1 then 1
												else 0 end,	--No response
	-- relative productivity
	prody = CASE WHEN				LA1908_01_I=1 THEN 0 
				WHEN				LA1908_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA1908_03=1 THEN 3
				WHEN			  LA1908_02=1 THEN 2
				WHEN			  LA1908_01=1 THEN 1
												ELSE 0 END,
	-- relative profitability
	profity = CASE WHEN				LA1907_01_I=1 THEN 0 
				WHEN				LA1907_04=1 THEN 4		-- Imputed or don't know
				WHEN			  LA1907_03=1 THEN 3
				WHEN			  LA1907_02=1 THEN 2
				WHEN			  LA1907_01=1 THEN 1
												ELSE 0 END,
	-- difficulty recruiting managers and professionals
	rec_manag = CASE WHEN		LA1701_01_I=1 THEN 0			-- imputed
				WHEN			LA1701_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1701_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1701_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1701_04=1	 THEN 5			-- not applicable
				WHEN			LA1701_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting technicians and associate professionals
	rec_tech = CASE WHEN		LA1702_01_I=1 THEN 0			-- imputed
				WHEN			LA1702_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1702_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1702_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1702_04=1	 THEN 5			-- not applicable
				WHEN			LA1702_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting tradespeople and related workers
	rec_trad = CASE WHEN		LA1703_01_I=1 THEN 0			-- imputed
				WHEN			LA1703_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1703_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1703_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1703_04=1	 THEN 5			-- not applicable
				WHEN			LA1703_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- difficulty recruiting all other occupations
	rec_other = CASE WHEN		LA1704_01_I=1 THEN 0			-- imputed
				WHEN			LA1704_01=1   THEN 1			-- no difficulty (??)
				WHEN			LA1704_02=1	 THEN 2			-- moderate difficulty
				WHEN			LA1704_03=1	 THEN 3			-- severe difficulty
				WHEN			LA1704_04=1	 THEN 5			-- not applicable
				WHEN			LA1704_05=1	 THEN 4			-- don't know
												ELSE 0 END,
	-- fraction of employees covered by collective employment agreement
	collec_emp = CASE WHEN		LA1800_01_I=1 THEN 0			-- imputed
				WHEN			LA1800_01=1	 THEN 1			-- zero
				WHEN			LA1800_02=1	 THEN 2			-- 10% or less
				WHEN			LA1800_03=1	 THEN 3			-- 50% or less
				WHEN			LA1800_04=1	 THEN 4			-- 90% or less
				WHEN			LA1800_05=1	 THEN 5			-- 91-100%
				WHEN			LA1800_06=1	 THEN 6			-- don't know
												ELSE 0 END,
	-- whether any foreign ownership
	any_internat = CASE WHEN	LA1200_01_I=1	THEN 0		-- imputed
				WHEN			LA1200_01=1		THEN 2		-- yes
				WHEN			LA1200_02=1		THEN 1		-- no
				WHEN			LA1200_03=1		THEN 3		-- don't know
											ELSE 0 END,
	-- percentage of foreign ownership
	perc_internat = CASE WHEN	LA1201_I=1		THEN 999	--imputed
				WHEN			LA1201 IS NOT NULL THEN CONVERT(FLOAT,LA1201) -- percentage when > 0
				WHEN			LA1200_02=1		THEN 0		-- none
											ELSE 999 END,
	-- number of employees who are managers or professionals; technicians/assoc profs; tradespeople; other
	emp_manag = ISNULL(LA1601,0),
	emp_tech = ISNULL(LA1602,0),
	emp_trad = ISNULL(LA1603,0),
	emp_other = ISNULL(LA1604,0)
	from ibuldd_clean_archive_dec_2018.dbo.fact_bos_enterprise_2016_mod_a)) b
where response_code not in ('C','Q','L','U')

--75,XXX rows





-------------------------------------------------------------
-- merging in pent
-------------------------------------------------------------

create table #ent_bos2(
		pent				CHAR(10)		NULL,
		enterprise_nbr			char(10)		NOT NULL,
		dim_year_key			int			NOT NULL,
		selection_weight		FLOAT			NOT NULL,
		final_weight			FLOAT			NOT NULL,
		adjusted_weight			FLOAT			NOT NULL,	
		comp				tinyint			NOT NULL,
		prody				TINYINT			NOT NULL,
		profity				TINYINT			NOT NULL,
		rec_manag			TINYINT			NOT NULL,
		rec_tech			TINYINT			NOT NULL,
		rec_trad			TINYINT			NOT NULL,
		rec_other			TINYINT			NOT NULL,
		collec_emp			TINYINT			NOT NULL,
		any_internat			TINYINT			NOT NULL,
		perc_internat			FLOAT			NOT NULL,
		emp_manag			SMALLINT		NOT NULL,
		emp_tech			SMALLINT		NOT NULL,
		emp_trad			SMALLINT		NOT NULL,
		emp_other			SMALLINT		NOT NULL
				primary key clustered(enterprise_nbr,dim_year_key))

insert into #ent_bos2(pent, enterprise_nbr,dim_year_key,selection_weight, final_weight, adjusted_weight, comp, prody, profity,
	rec_manag, rec_tech, rec_trad, rec_other, collec_emp, any_internat, perc_internat, emp_manag, emp_tech, emp_trad, emp_other)
select
PENT.pent,
COMP.enterprise_nbr,
COMP.dim_year_key,
selection_weight,
final_weight,
adjusted_weight,
comp,
prody,
profity,
rec_manag, 
rec_tech, 
rec_trad, 
rec_other, 
collec_emp,
any_internat,
perc_internat,
emp_manag, 
emp_tech, 
emp_trad, 
emp_other
from #ent_bos_competition COMP
LEFT JOIN [ibuldd_research_datalab].[STATSNZ\dl_RFabling].[pent_IDI_20181020] PENT
	ON COMP.enterprise_nbr = PENT.enterprise_nbr
WHERE (dim_year_key >= PENT.start_month OR PENT.start_month IS NULL)
		
-- 75,XXX rows







-------------------------------------------------------------
-- merging in industry and fte
-------------------------------------------------------------

-- save as csv file Wgap_compet_v3.csv


SELECT
	COMP.pent,
	COMP.enterprise_nbr,
	COMP.dim_year_key,
	selection_weight,
	final_weight,
	adjusted_weight,
	comp,
	prody,
	profity,
	rec_manag, 
	rec_tech, 
	rec_trad, 
	rec_other, 
	collec_emp,
	any_internat,
	perc_internat,
	emp_manag, 
	emp_tech, 
	emp_trad, 
	emp_other, 
	IND.anz06_4d,
	FTE.fte,
	FTE.WP,
	FTE.rme_no_WP
FROM #ent_bos2 COMP
LEFT JOIN [IDI_Sandpit].[clean_read_IR].[pent_ind_IDI_20181020_RFabling] IND
	ON COMP.pent=IND.pent 
LEFT JOIN [ibuldd_research_datalab].[STATSNZ\dl_RFabling].[pent_year_L_IDI_20181020] FTE
	ON COMP.pent=FTE.pent AND COMP.dim_year_key=FTE.dim_year_key



-- 75,XXX rows


